package edu.jptest.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import edu.jptest.bean.QuestionSubject;
import edu.jptest.util.GetDataBaseConnection;

public class QuestionSubjectDAO {
	// 插入题目
	public boolean insertQuestionSubject(QuestionSubject questionSubject) {
		GetDataBaseConnection getDataBaseConnection = new GetDataBaseConnection();
		Connection connection = getDataBaseConnection.getConnection();
		PreparedStatement pstm = null;
		try {
			String sql = "insert into question_subject(question_subject_content,question_book_num) values(?,?)";
			pstm = connection.prepareStatement(sql);
			pstm.setString(1, questionSubject.getQuestionSubjectContent());
			pstm.setInt(2, questionSubject.getQuestionBookNum());
			int result = pstm.executeUpdate();
			if (result == 1) {
				return true;
			} else {
				return false;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (connection != null)
				try {
					connection.close();
				} catch (SQLException e) {
					;
				}
			connection = null;
			if (pstm != null) {
				try {
					pstm.close();
				} catch (SQLException e) {
					;
				}
			}
			pstm = null;
		}
		return false;
	}

	// 删除题目
	public boolean deleteQuestionSubject(int questionSubject_id) {
		GetDataBaseConnection getDataBaseConnection = new GetDataBaseConnection();
		Connection connection = getDataBaseConnection.getConnection();
		PreparedStatement pstm = null;
		try {
			String sql = "delete from question_subject where question_subject_id=?";
			pstm = connection.prepareStatement(sql);
			pstm.setInt(1, questionSubject_id);
			int result = pstm.executeUpdate();
			if (result == 1) {
				return true;
			} else {
				return false;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (connection != null)
				try {
					connection.close();
				} catch (SQLException e) {
					;
				}
			connection = null;
			if (pstm != null) {
				try {
					pstm.close();
				} catch (SQLException e) {
					;
				}
			}
			pstm = null;
		}
		return false;
	}

	// 修改题目
	public boolean updateQuestionSubject(QuestionSubject questionSubject) {
		GetDataBaseConnection getDataBaseConnection = new GetDataBaseConnection();
		Connection connection = getDataBaseConnection.getConnection();
		PreparedStatement pstm = null;
		try {
			String sql = "update question_subject set question_subject_content=?,question_book_num=? where question_subject_id=?";
			pstm = connection.prepareStatement(sql);
			pstm.setString(1, questionSubject.getQuestionSubjectContent());
			pstm.setInt(2, questionSubject.getQuestionBookNum());
			pstm.setInt(3, questionSubject.getQuestionSubjectId());
			int result = pstm.executeUpdate();
			if (result == 1) {
				return true;
			} else {
				return false;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (connection != null)
				try {
					connection.close();
				} catch (SQLException e) {
					;
				}
			connection = null;
			if (pstm != null) {
				try {
					pstm.close();
				} catch (SQLException e) {
					;
				}
			}
			pstm = null;
		}
		return false;
	}

	// 通过题目id，查询题目对象
	public QuestionSubject getQuestionSubjectByQuestionSubjectId(
			int questionSubject_id) {
		GetDataBaseConnection getDataBaseConnection = new GetDataBaseConnection();
		Connection connection = getDataBaseConnection.getConnection();
		ResultSet resultSet = null;
		QuestionSubject questionSubject = new QuestionSubject();
		PreparedStatement pstm = null;
		try {
			String sql = "select * from question_subject where question_subject_id=?";
			pstm = connection.prepareStatement(sql);
			pstm.setInt(1, questionSubject_id);
			resultSet = pstm.executeQuery();
			while (resultSet.next()) {
				questionSubject.setQuestionSubjectId(resultSet.getInt(1));
				questionSubject.setQuestionSubjectContent(resultSet
						.getString(2));
				questionSubject.setQuestionBookNum(resultSet.getInt(3));
				return questionSubject;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (connection != null)
				try {
					connection.close();
				} catch (SQLException e) {
					;
				}
			connection = null;
			if (pstm != null) {
				try {
					pstm.close();
				} catch (SQLException e) {
					;
				}
			}
			pstm = null;
			if (resultSet != null) {
				try {
					resultSet.close();
				} catch (SQLException e) {
					;
				}
			}
			resultSet = null;
		}
		return null;
	}

	// 通过题目序号和内容，查询题目id
	public int getQuestionSubjectIdByQuestionSubject(int questionSubjectNum,String questionSubjectContent) {
		GetDataBaseConnection getDataBaseConnection = new GetDataBaseConnection();
		Connection connection = getDataBaseConnection.getConnection();
		ResultSet resultSet = null;
		PreparedStatement pstm = null;
		try {
			String sql = "select question_subject_id from question_subject where question_book_num=? and question_subject_content=?";
			pstm = connection.prepareStatement(sql);
			pstm.setInt(1, questionSubjectNum);
			pstm.setString(2, questionSubjectContent);
			resultSet = pstm.executeQuery();
			while (resultSet.next()) {
				return resultSet.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (connection != null)
				try {
					connection.close();
				} catch (SQLException e) {
					;
				}
			connection = null;
			if (pstm != null) {
				try {
					pstm.close();
				} catch (SQLException e) {
					;
				}
			}
			pstm = null;
			if (resultSet != null) {
				try {
					resultSet.close();
				} catch (SQLException e) {
					;
				}
			}
			resultSet = null;
		}
		return 0;
	}

	// 获得所有题目id
	public List<Integer> getAllQuestionSubjectsId() {
		GetDataBaseConnection getDataBaseConnection = new GetDataBaseConnection();
		Connection connection = getDataBaseConnection.getConnection();
		ResultSet resultSet = null;
		PreparedStatement pstm = null;
		ArrayList<Integer> questionSubjectsId = new ArrayList<Integer>();
		try {
			String sql = "select distinct question_subject_id from question_subject";
			pstm = connection.prepareStatement(sql);
			resultSet = pstm.executeQuery();
			while (resultSet.next()) {
				questionSubjectsId.add(resultSet.getInt(1));
			}
			return questionSubjectsId;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (connection != null)
				try {
					connection.close();
				} catch (SQLException e) {
					;
				}
			connection = null;
			if (pstm != null) {
				try {
					pstm.close();
				} catch (SQLException e) {
					;
				}
			}
			pstm = null;
			if (resultSet != null) {
				try {
					resultSet.close();
				} catch (SQLException e) {
					;
				}
			}
			resultSet = null;
		}
		return null;
	}
}
